<?php
include "db.conf.php";
$sTableName="2014_w_la";
$sTeamWork="tw_1";
$sPeerEval="peer_eval_1";

if ((isset($_REQUEST['type']))and($_REQUEST['type']=='update'))
{
	$sqlConnect = new mysqli($sServer,$sUser,$sPassword,$sDatabaseName);
	
	foreach ($_REQUEST as $sStudentID => $nPeerEval)
	{
		$sQuery = "UPDATE `".$sTableName."` SET `".$sPeerEval."`=".$nPeerEval." WHERE `student_number`=".$sStudentID;
		$sqlResult=$sqlConnect->query($sQuery);
	
		$nAffectedRows=$sqlConnect->affected_rows;
	
		if ($nAffectedRows>0)
		{
			echo "update ".$sStudentID." to ".$nPeerEval.": done<br/>";
		}
	}
	$sqlConnect->close();
	die("Update Done");
}
	?>
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Untitled Document</title>
</head>

<body><form name="frmTeamEval" action="teamEval.php?type=update" method="post"><pre>
<?php
include $_SERVER['DOCUMENT_ROOT'].'/Classes/PHPExcel/IOFactory.php';

//	Read Excel Directory//
$dirExcelFiles="excels/";
$dirRenamedExcelFiles="renamed_excels/";	
$aExcelFiles=[];
if ($handle = opendir($dirExcelFiles)) {
while (false !== ($entry = readdir($handle))) {
	if ($entry != "." && $entry != "..") {
		$sFileName=substr($entry,0,10);
		$sFileType=pathinfo($entry,PATHINFO_EXTENSION);
		$sNewFileName=$dirExcelFiles.$sFileName.".".$sFileType;
		rename($dirExcelFiles.$entry,$sNewFileName);
		$aExcelFiles[$sFileName]=$sNewFileName;
	}
}
closedir($handle);
}
//print_r($aExcelFiles);
// End Read directory


$sqlConnect = new mysqli($sServer,$sUser,$sPassword,$sDatabaseName);
$sqlConnect->query("SET NAMES 'UTF8'");

if ($sqlConnect ->connect_error) {
	die("Can't connect to database");
}

//	select teams
for ($nTeam=1;$nTeam<21;$nTeam++)
{	
	echo"<h1>".$nTeam."</h1>";
	//Reset array of students
	$aStudents=[];
	
	//	Total submitted evaluations
	$nSubmittedEval=0;
	
	//	Get the students in $nTeam
	$sQuery = "SELECT `student_number`,`name`,`".$sTeamWork."` FROM `".$sTableName."` WHERE `team`=".$nTeam;

	$sqlResult=$sqlConnect->query($sQuery);
	//	Get number of students in Teams
	$nTeamSize = $sqlResult->num_rows;
	
	while ($row = $sqlResult->fetch_array(MYSQLI_ASSOC))   
	{
		//$sStudentNumber=$row["student_number"];
		$aStudents[$row["name"]]=["student_number"=>$row["student_number"],"score"=>$row[$sTeamWork],"peer_eval"=>0,"submitted"=>0];
	}
	//	End getting the students in $nTeam
	
	//	Prepare table
	echo	"<table cellspacing='10'><tr><td>";
	//	Scan through students in $nTeam
	foreach ($aStudents as $sStudentName => $aStudentInfo)
	{	
		//Reset total distribution
		$nTotalEvaluation=0;
		$nSubmittedEval++;
	    
		//	Evaluation file name
		$sFileName=$aStudentInfo["student_number"];
		
		//	If student $sStudentNumber submit file name.
		if (array_key_exists($sFileName,$aExcelFiles))
		{
			//  student submited peer evaluation
			$aStudents[$sStudentName]["submitted"]=1;
			echo "submitted</br>";
			
			//	start working with excel
			$inputFileName=$aExcelFiles[$sFileName];
			//  Read your Excel workbook
			try 
			{
				$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
				$objReader = PHPExcel_IOFactory::createReader($inputFileType);
				$objPHPExcel = $objReader->load($inputFileName);
			} 
			catch(Exception $e) 
			{
				echo('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
			}
			
			//  Get worksheet dimensions
			$sheet = $objPHPExcel->getActiveSheet();
// 			$nSheetCount=$objPHPExcel->getSheetCount();
			
			//	find sheet
			/*$nSheetIndex=-1;
			$sCellText="";
			while ($sCellText=="")
			{
				$nSheetIndex++;
				$sheet=$objPHPExcel->getSheet($nSheetIndex);
				$sCellText = $sheet->getCell('B2')->getValue();
				echo "sheet $nSheetIndex: $sCellText ".($sheet->getCell("A2")->getValue());
			}*/
			$highestRow = $sheet->getHighestRow();
			$highestColumn = $sheet->getHighestColumn();
			
			//  Loop through each row of the worksheet in turn
			//  Read rows of data into an array
			$excelData = $sheet->rangeToArray('A2:B'.($nTeamSize+1) ,
					NULL,
					TRUE,
					FALSE);

			for ($row=0;$row<$nTeamSize;$row++)
			{
			    //   Remove spaces if exist
    			//$excelData[$row][0]= preg_replace('/\s+/', '', $excelData[$row][0]);
				 $excelData[$row][0] =str_replace(" ","",$excelData[$row][0]);
			    //   update total evaluation points
    		    $nTotalEvaluation +=$excelData[$row][1];
			    echo ($row." ".substr($excelData[$row][0],0,15)." ".$excelData[$row][1]."</br>");
			}
			
			//   update students' peer eval			
			for ($row=0;$row<$nTeamSize;$row++)
			{
				//	if found student name in sheet - no error
			    if (array_key_exists($excelData[$row][0],$aStudents))
	            {
	                $aStudents[$excelData[$row][0]]["peer_eval"]+=($excelData[$row][1]/$nTotalEvaluation);
	            }
	            else	// if student name wasn't found - error
	            {
	                echo "students name <strong>".substr($excelData[$row][0],0,15)."</strong> not exists\n";
	            }
		}
		
			//  Insert row data array into your database of choice here
			echo "<font color=\"red\"><strong>Total: ".$nTotalEvaluation."</strong></font></br>";
		}
		else {echo "<br/>";}
	}
	echo	"</td><td>";
	foreach ($aStudents as $sStudentName =>$aStudentInfo)
	{
		//	round peer eval
		$aStudentInfo["peer_eval"]=min($aStudentInfo["peer_eval"]*$nTeamSize*$aStudentInfo["submitted"]/$nSubmittedEval,1);
		
		// print student input
		echo $sStudentName.": <br>\t peer eval:<input type='text' name='".$aStudentInfo["student_number"]."' width='10'value='".$aStudentInfo["peer_eval"]."'/><br>";
		echo "\t submitted:".$aStudentInfo["submitted"]."<br>";
		//echo "\t peer eval:".$aStudentInfo["peer_eval"]."<br>";
		echo "\t student number:".$aStudentInfo["student_number"]."<br>";
	}
	echo	"</td></tr></table>";
}
$sqlConnect->close();
?></pre>
<button type="submit">Submit</button></form>
</body>
</html>
